﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class PodDA : IPodDA
    {
        private string BOL_ID = "@BOL_ID";
        private string C_ADDRESS = "@C_ADDRESS";
        private string C_PHONE = "@C_PHONE";
        private string CARRIER_ID = "@CARRIER_ID";
        private string CONTACT = "@CONTACT";
        private string CREATED_BY = "@CREATED_BY";
        private string CUSTOMER_ID = "@CUSTOMER_ID";
        private string CUSTOMER_NAME = "@CUSTOMER_NAME";
        private string DELIVERY_ID = "@DELIVERY_ID";
        private string DRIVER_ID = "@DRIVER_ID";
        private string EQUIPMENT_ID = "@EQUIPMENT_ID";
        private string EXCEPT_ARRIVERD_DATE = "@EXCEPT_ARRIVERD_DATE";
        private string OWNER_ID = "@OWNER_ID";
        private string PICKUP_TYPE = "@PICKUP_TYPE";
        private string POD_ID = "@POD_ID";
        private string PODIMG = "@PODIMG";
        private string REMARK = "@REMARK";
        private string SIGNED_BY = "@SIGNED_BY";
        private string SIGNED_DATE = "@SIGNED_DATE";
        private string SQL_CHECK_POD_ID_UNIQUE = " SELECT COUNT(1) FROM @_@POD WHERE POD_ID = @POD_ID ";
        private string SQL_DELETE_POD = " DELETE FROM @_@POD WHERE POD_ID = @POD_ID ";
        private string SQL_INSERT_POD = " INSERT INTO @_@POD ( WH_ID, POD_ID, PODIMG, BOL_ID, DELIVERY_ID, STATUS, CARRIER_ID, OWNER_ID, EQUIPMENT_ID, DRIVER_ID, EXCEPT_ARRIVERD_DATE, SIGNED_DATE, SIGNED_BY, TRANSPORT_MODE, PICKUP_TYPE, CUSTOMER_ID, CUSTOMER_NAME, CONTACT, C_PHONE, C_ADDRESS, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REMARK, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @POD_ID, @PODIMG, @BOL_ID, @DELIVERY_ID, @STATUS, @CARRIER_ID, @OWNER_ID, @EQUIPMENT_ID, @DRIVER_ID, @EXCEPT_ARRIVERD_DATE, @SIGNED_DATE, @SIGNED_BY, @TRANSPORT_MODE, @PICKUP_TYPE, @CUSTOMER_ID, @CUSTOMER_NAME, @CONTACT, @C_PHONE, @C_ADDRESS, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @REMARK, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_POD = "SELECT  POD.ROWID, POD.WH_ID, POD.POD_ID,POD.PODIMG, POD.BOL_ID, POD.DELIVERY_ID,  POD.STATUS as STATUS,  T.[TEXT] POD_STATUS,  POD.CARRIER_ID, POD.OWNER_ID, POD.EQUIPMENT_ID, POD.DRIVER_ID, POD.EXCEPT_ARRIVERD_DATE, POD.SIGNED_DATE, POD.SIGNED_BY, POD.TRANSPORT_MODE, POD.PICKUP_TYPE, POD.CUSTOMER_ID, POD.CUSTOMER_NAME, POD.CONTACT, POD.C_PHONE, POD.C_ADDRESS, POD.UDF1, POD.UDF2, POD.UDF3, POD.UDF4, POD.UDF5, POD.UDF6, POD.UDF7, POD.UDF8, POD.UDF9, POD.UDF10, POD.REMARK, POD.TS, POD.CREATED_BY, POD.CREATED_DATE, POD.UPDATED_BY, POD.UPDATED_DATE    FROM @_@POD as POD  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = POD.STATUS AND SCD.SYS_CODE_TYPE = 'PodStatus'    LEFT JOIN sysadm.TRANSLATION T ON 'PodStatus.'+ POD.STATUS = T.ITEM_ID   AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE  INNER JOIN SYSADM.USER_OWNER UO ON POD.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
        private string SQL_UPDATE_POD = " UPDATE @_@POD SET WH_ID = @WH_ID, PODIMG = @PODIMG, BOL_ID = @BOL_ID, DELIVERY_ID = @DELIVERY_ID, STATUS = @STATUS, CARRIER_ID = @CARRIER_ID, OWNER_ID = @OWNER_ID, EQUIPMENT_ID = @EQUIPMENT_ID, DRIVER_ID = @DRIVER_ID, EXCEPT_ARRIVERD_DATE = @EXCEPT_ARRIVERD_DATE, SIGNED_DATE = @SIGNED_DATE, SIGNED_BY = @SIGNED_BY, TRANSPORT_MODE = @TRANSPORT_MODE, PICKUP_TYPE = @PICKUP_TYPE, CUSTOMER_ID = @CUSTOMER_ID, CUSTOMER_NAME = @CUSTOMER_NAME, CONTACT = @CONTACT, C_PHONE = @C_PHONE, C_ADDRESS = @C_ADDRESS, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, REMARK = @REMARK, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE POD_ID = @POD_ID  ";
        private string STATUS = "@STATUS";
        private string TRANSPORT_MODE = "@TRANSPORT_MODE";
        private string TS = "@TS";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";

        public bool CheckPodIDUnique(string podID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@POD_ID", podID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_POD_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckPodIDUnique(DataBase dataBase, DbTransaction tran, string podID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@POD_ID", podID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_POD_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeletePod(List<string> podID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < podID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@POD_ID", podID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_POD.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeletePod(DataBase dataBase, DbTransaction tran, List<string> podID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < podID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@POD_ID", podID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_POD.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllPod(string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_POD.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetAllPod(DataBase dataBase, DbTransaction tran, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_POD.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public PodInfo GetPodByID(string podID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_POD.Replace("@_@", whLoginID) + " WHERE POD_ID = @POD_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@POD_ID", podID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            PodInfo podInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    podInfo = new PodInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["POD_ID"].ToString(), reader["PODIMG"].ToString(), reader["BOL_ID"].ToString(), reader["DELIVERY_ID"].ToString(), reader["STATUS"].ToString(), reader["CARRIER_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["EQUIPMENT_ID"].ToString(), reader["DRIVER_ID"].ToString(), (reader["EXCEPT_ARRIVERD_DATE"].ToString() != "") ? DateTime.Parse(reader["EXCEPT_ARRIVERD_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["SIGNED_DATE"].ToString() != "") ? DateTime.Parse(reader["SIGNED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["SIGNED_BY"].ToString(), reader["TRANSPORT_MODE"].ToString(), reader["PICKUP_TYPE"].ToString(), reader["CUSTOMER_ID"].ToString(), reader["CUSTOMER_NAME"].ToString(), reader["CONTACT"].ToString(), reader["C_PHONE"].ToString(), reader["C_ADDRESS"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return podInfo;
        }

        public PodInfo GetPodByID(DataBase dataBase, DbTransaction tran, string podID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_POD.Replace("@_@", whLoginID) + " WHERE POD_ID = @POD_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@POD_ID", podID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            PodInfo podInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                podInfo = new PodInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["POD_ID"].ToString(), reader["PODIMG"].ToString(), reader["BOL_ID"].ToString(), reader["DELIVERY_ID"].ToString(), reader["STATUS"].ToString(), reader["CARRIER_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["EQUIPMENT_ID"].ToString(), reader["DRIVER_ID"].ToString(), (reader["EXCEPT_ARRIVERD_DATE"].ToString() != "") ? DateTime.Parse(reader["EXCEPT_ARRIVERD_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["SIGNED_DATE"].ToString() != "") ? DateTime.Parse(reader["SIGNED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["SIGNED_BY"].ToString(), reader["TRANSPORT_MODE"].ToString(), reader["PICKUP_TYPE"].ToString(), reader["CUSTOMER_ID"].ToString(), reader["CUSTOMER_NAME"].ToString(), reader["CONTACT"].ToString(), reader["C_PHONE"].ToString(), reader["C_ADDRESS"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return podInfo;
        }

        public DataSet GetPodByQueryList(List<string> sqlWhere, PodQueryEntity podQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_POD.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!podQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, podQuery.CurrentPage, podQuery.PageSize, podQuery.SortField, podQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetPodByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, PodQueryEntity podQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_POD.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (podQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, podQuery.CurrentPage, podQuery.PageSize, podQuery.SortField, podQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertPod(PodInfo podInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    podInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Pod_Parameters(podInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_POD.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertPod(DataBase dataBase, DbTransaction tran, PodInfo podInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    podInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Pod_Parameters(podInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_POD.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_Pod_Parameters(PodInfo podInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.POD_ID, SqlDbType.VarChar, 20), new SqlParameter(this.PODIMG, SqlDbType.VarChar, 300), new SqlParameter(this.BOL_ID, SqlDbType.VarChar, 20), new SqlParameter(this.DELIVERY_ID, SqlDbType.VarChar, 30), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.CARRIER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.EQUIPMENT_ID, SqlDbType.VarChar, 20), new SqlParameter(this.DRIVER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.EXCEPT_ARRIVERD_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.SIGNED_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.SIGNED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.TRANSPORT_MODE, SqlDbType.VarChar, 20), new SqlParameter(this.PICKUP_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.CUSTOMER_ID, SqlDbType.VarChar, 30), 
                new SqlParameter(this.CUSTOMER_NAME, SqlDbType.VarChar, 30), new SqlParameter(this.CONTACT, SqlDbType.VarChar, 30), new SqlParameter(this.C_PHONE, SqlDbType.VarChar, 30), new SqlParameter(this.C_ADDRESS, SqlDbType.VarChar, 100), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.REMARK, SqlDbType.VarChar, 100), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 30), 
                new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 30)
             };
            if (!string.IsNullOrEmpty(podInfo.WhID))
            {
                paramArray[0].Value = podInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = podInfo.PodID;
            if (!string.IsNullOrEmpty(podInfo.Podimg))
            {
                paramArray[2].Value = podInfo.Podimg;
            }
            else
            {
                paramArray[2].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podInfo.BolID))
            {
                paramArray[3].Value = podInfo.BolID;
            }
            else
            {
                paramArray[3].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podInfo.DeliveryID))
            {
                paramArray[4].Value = podInfo.DeliveryID;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            paramArray[5].Value = podInfo.Status;
            if (!string.IsNullOrEmpty(podInfo.CarrierID))
            {
                paramArray[6].Value = podInfo.CarrierID;
            }
            else
            {
                paramArray[6].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podInfo.OwnerID))
            {
                paramArray[7].Value = podInfo.OwnerID;
            }
            else
            {
                paramArray[7].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podInfo.EquipmentID))
            {
                paramArray[8].Value = podInfo.EquipmentID;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podInfo.DriverID))
            {
                paramArray[9].Value = podInfo.DriverID;
            }
            else
            {
                paramArray[9].Value = DBNull.Value;
            }
            DateTime CS00003 = new DateTime();
            if (podInfo.ExceptArriverdDate != CS00003)
            {
                paramArray[10].Value = podInfo.ExceptArriverdDate;
            }
            else
            {
                paramArray[10].Value = DBNull.Value;
            }
            if (podInfo.SignedDate != new DateTime())
            {
                paramArray[11].Value = podInfo.SignedDate;
            }
            else
            {
                paramArray[11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podInfo.SignedBy))
            {
                paramArray[12].Value = podInfo.SignedBy;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podInfo.TransportMode))
            {
                paramArray[13].Value = podInfo.TransportMode;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podInfo.PickupType))
            {
                paramArray[14].Value = podInfo.PickupType;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podInfo.CustomerID))
            {
                paramArray[15].Value = podInfo.CustomerID;
            }
            else
            {
                paramArray[15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podInfo.CustomerName))
            {
                paramArray[0x10].Value = podInfo.CustomerName;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podInfo.Contact))
            {
                paramArray[0x11].Value = podInfo.Contact;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podInfo.CPhone))
            {
                paramArray[0x12].Value = podInfo.CPhone;
            }
            else
            {
                paramArray[0x12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podInfo.CAddress))
            {
                paramArray[0x13].Value = podInfo.CAddress;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podInfo.Udf1))
            {
                paramArray[20].Value = podInfo.Udf1;
            }
            else
            {
                paramArray[20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podInfo.Udf2))
            {
                paramArray[0x15].Value = podInfo.Udf2;
            }
            else
            {
                paramArray[0x15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podInfo.Udf3))
            {
                paramArray[0x16].Value = podInfo.Udf3;
            }
            else
            {
                paramArray[0x16].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podInfo.Udf4))
            {
                paramArray[0x17].Value = podInfo.Udf4;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podInfo.Udf5))
            {
                paramArray[0x18].Value = podInfo.Udf5;
            }
            else
            {
                paramArray[0x18].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podInfo.Udf6))
            {
                paramArray[0x19].Value = podInfo.Udf6;
            }
            else
            {
                paramArray[0x19].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podInfo.Udf7))
            {
                paramArray[0x1a].Value = podInfo.Udf7;
            }
            else
            {
                paramArray[0x1a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podInfo.Udf8))
            {
                paramArray[0x1b].Value = podInfo.Udf8;
            }
            else
            {
                paramArray[0x1b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podInfo.Udf9))
            {
                paramArray[0x1c].Value = podInfo.Udf9;
            }
            else
            {
                paramArray[0x1c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podInfo.Udf10))
            {
                paramArray[0x1d].Value = podInfo.Udf10;
            }
            else
            {
                paramArray[0x1d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podInfo.Remark))
            {
                paramArray[30].Value = podInfo.Remark;
            }
            else
            {
                paramArray[30].Value = DBNull.Value;
            }
            paramArray[0x1f].Value = podInfo.CreatedBy;
            paramArray[0x20].Value = podInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdatePod(PodInfo podInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    podInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Pod_Parameters(podInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_POD.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdatePod(DataBase dataBase, DbTransaction tran, PodInfo podInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    podInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Pod_Parameters(podInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_POD.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

